﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace DataConverter
{
    class Program
    {
        static void Main(string[] args)
        {
            string path = @"E:\iPark\Database\TestDataUTF16.csv";

            string output = @"E:\iPark\Database\InsertData.sqlce";

            using (StreamWriter sw = new StreamWriter(output))
            {
                using (StreamReader sr = new StreamReader(path))
                {
                    string header = sr.ReadLine();

                    while (!sr.EndOfStream)
                    {
                        string data = sr.ReadLine();

                        string[] infos = data.Split(',');

                        string name = infos[1];
                        string owner = infos[2];
                        int capacity = int.Parse(infos[3]);
                        int parkingMech = int.Parse(infos[4]);
                        int parkingNonMech = int.Parse(infos[5]);
                        string priceInfo = infos[6];
                        string timeInfo = infos[7];
                        string admin = infos[8];

                        float longitude = float.MinValue;
                        float latitude = float.MinValue;

                        if (!string.IsNullOrEmpty(infos[26]))
                        {
                            longitude = float.Parse(infos[26]);
                        }
                        else if (!string.IsNullOrEmpty(infos[11]))
                        {
                            longitude = float.Parse(infos[11]);
                        }

                        if (!string.IsNullOrEmpty(infos[27]))
                        {
                            latitude = float.Parse(infos[27]);
                        }
                        else if (!string.IsNullOrEmpty(infos[12]))
                        {
                            latitude = float.Parse(infos[12]);
                        }

                        string pinyin = infos[24];
                        string keyword = infos[25];

                        string guid = System.Guid.NewGuid().ToString();

                        //create table "Parking_Info"  (
                        //   "Guid"               nvarchar(64)                    not null,
                        //   "Admin"              nvarchar(64),
                        //   "FullName"           nvarchar(500),
                        //   "ShortName1"         nvarchar(500),
                        //   "ShortName2"         nvarchar(500),
                        //   "ShortName3"         nvarchar(500),
                        //   "Owner"              nvarchar(500),
                        //   "Capacity"           int,
                        //   "ParkingMech"        int,
                        //   "ParkingNonMech"     int,
                        //   "PriceInfo"          nvarchar(1000),
                        //   "TimeInfo"           nvarchar(1000),
                        //   "Longitude_Google"   FLOAT,
                        //   "Latitude_Google"    FLOAT,
                        //   "Longitude_Baidu"    FLOAT,
                        //   "Latitude_Baidu"     FLOAT,
                        //   "ChildAdmin1"        nvarchar(64),
                        //   "ChildAdmin2"        nvarchar(64),
                        //   "Type"               int,
                        //   constraint PK_PARKING_INFO primary key ("Guid")
                        //);
                        string sql = String.Format("INSERT INTO Parking_Info VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},{8},{9},'{10}','{11}',{12},{13},{14},{15},'{16}','{17}',{18});",
                            guid, admin, name, pinyin, keyword, "", owner, capacity, parkingMech, parkingNonMech, priceInfo,
                            timeInfo, float.MinValue, float.MinValue, longitude, latitude, "", "", "0");

                        sw.WriteLine(sql);
                    }
                }
            }
        }
    }
}
